package com.drore.cloud.sdkjdbc.core;

import com.drore.cloud.sdkjdbc.Jdbc.JDBCTemplate;
import com.drore.cloud.sdkjdbc.exception.DataSdkException;
import com.drore.cloud.sdkjdbc.model.Pagination;
import com.drore.cloud.sdkjdbc.model.QueryParam;
import com.drore.cloud.sdkjdbc.model.SqlParameter;
import com.drore.cloud.sdkjdbc.utils.MapClearUtil;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.gson.Gson;

import java.sql.SQLException;
import java.util.*;

/***
 * 浙江卓锐科技股份有限公司 版权所有@Copyright 2016
 * 说明
 * 项目名称
 * @since:cloud-ims 1.0
 * @author <a href="mailto:baoec@drore.com">baoec@drore.com </a>
 * 2018/04/23 11:18
 */
public class JdbcRunner extends JDBCTemplate {

    /**
     * 根据主键id获取单条数据*
     * ***/
    public Map<String, Object> queryOne(String resource_name, String id) {
        if(id==null){
            throw new DataSdkException("you must check you request params about id is not null");
        }
        //预编译sql
        StringBuffer sql=new StringBuffer("SELECT *  FROM ");
        sql.append(resource_name);
        sql.append(" where id = ?");
        //查询条件拼装
        ArrayList<SqlParameter> objects = Lists.newArrayList();
        SqlParameter parameter = new SqlParameter("term", id);
        objects.add(parameter);
        Map map = queryForMap(sql.toString(),objects);
        return map;
    }

    /**
     * 根据查询条件获取 首条数据
     * map 中存放的是 key:字段名  value 字段值
     * 此方法只支持完全匹配
     * ***/
    public Map<String, Object> queryFirstByRName(String resource_name, Object data) {
        Gson gson = new Gson();
        Map<String,Object> map = gson.fromJson(gson.toJson(data), Map.class);
        //查具体数据
        StringBuffer sql=new StringBuffer("SELECT *  FROM ");
        sql.append(resource_name);

        ArrayList<SqlParameter> objects = Lists.newArrayList();
        if(map.size()>0) {
            sql.append(" where ");
            String cp = "";
            for (Object key : map.keySet()) {
                sql.append(cp).append(Objects.toString(key)).append(" = ? ");
                cp = " and ";
                SqlParameter parameter = new SqlParameter("term", map.get(key));
                objects.add(parameter);
            }
        }
        List<Map<String, Object>> list = queryForList(sql.toString(),objects);
        if(list.size()>0){
            map=list.get(0);
        }
        return map;
    }



    public Pagination queryListByExample(String resourceName){
        return queryListByExample(resourceName,1,10);
    }
    public Pagination queryListByExample(String resourceName,Integer current_page,Integer page_size){
        return queryListByExample(resourceName,null,current_page,page_size);
    }
    public Pagination queryListByExample(String resource_name, Map map){
        return queryListByExample(resource_name,map,1,10);
    }
    //通过字段精确匹配数据
    public Pagination queryListByExample(String resource_name, Map map,Integer current_page,Integer page_size){
        QueryParam query=new QueryParam(current_page,page_size);
        QueryParam.Param param=new QueryParam.Param();
        if(map!=null){
            param.setTerm(map);
        }
        query.setParam(param);
        return queryListByExample(resource_name,query);
    }
    /**
     * 最终通用
     * 完全匹配/模糊匹配/区间范围
     * 防止sql注入
     * ***/

    public Pagination queryListByExample(String resource_name, QueryParam query) {

        ArrayList<SqlParameter> objects = Lists.newArrayList();

        Integer current_page=query.getCurrent_page();
        Integer page_size=query.getPage_size();

        //查总数
        StringBuffer sqlcount=new StringBuffer("SELECT count(*)  FROM ");
        //查询内容 核心拼接
        StringBuffer sqlcontent=new StringBuffer();
        sqlcontent.append(resource_name);

        //query_data= MapClearUtil.clear(query_data);
        if(query.getParam()!=null){
            if(query.getParam().getParamSize()>0){
                sqlcontent.append(" where ");
            }
            int tynum=0;
            //模糊匹配
            if(query.getParam().getFuzzy().size()>0){
                for (Map.Entry<String,Object> entry:query.getParam().getFuzzy().entrySet()) {

                    sqlcontent.append(tynum==0?"":" and ").append(entry.getKey()).append(" like ").append("?");
                    SqlParameter sqlParameter = new SqlParameter("fuzzy",entry.getValue());
                    objects.add(sqlParameter);
                    tynum++;
                }
            }
            //精确匹配
            if(query.getParam().getTerm().size()>0){
                for (Map.Entry<String,Object> entry:query.getParam().getTerm().entrySet()) {
                    sqlcontent.append(tynum==0?"":" and ").append(entry.getKey()).append(" = ").append("?");
                    SqlParameter sqlParameter = new SqlParameter("term",entry.getValue());
                    objects.add(sqlParameter);
                    tynum++;
                }
            }
            //范围查询
            if(query.getParam().getRange().size()>0){
                for (Map.Entry<String,Object> entry:query.getParam().getRange().entrySet()) {
                    String value = Objects.toString(entry.getValue());
                    if(value.indexOf("->")!=-1){
                        //包含 说明 范围是双条件
                        String[] split = value.split("->");
                        if(split.length!=2){
                            throw new DataSdkException("范围 符号 -> 使用有误 请检查");
                        }
                        sqlcontent.append(tynum==0?"":" and ").append(entry.getKey()).append(" between  ").append("?").append(" and ").append("?");
                        SqlParameter sqlParameter0 = new SqlParameter("rangeOne",split[0]);
                        SqlParameter sqlParameter1 = new SqlParameter("rangeOne",split[1]);
                        objects.add(sqlParameter0);
                        objects.add(sqlParameter1);
                        tynum++;
                    }else {
                        //rangeTwo 解析范围值参数
                        Map<String,String> rangeMap = ImmutableMap.of("gt",">","lt","<","gte",">=","lte","<=","ne","!=");
                        //不包含 说明 范围是单条件
                        Map<String,Object> rangMap = (Map)entry.getValue();
                        if(rangMap.size()==1){
                            for (Map.Entry<String,Object> rangentry  : rangMap.entrySet()) {
                                String operator = rangeMap.get(rangentry.getKey());
                                if(operator==null){
                                    throw new DataSdkException("rangeTwo 方式超出范围 请联系开发者");
                                }
                                sqlcontent.append(tynum==0?"":" and ").append(entry.getKey()).append(operator).append(" ").append("?");
                                SqlParameter sqlParameter = new SqlParameter("rangeTwo",rangentry.getValue());
                                objects.add(sqlParameter);
                                tynum++;
                            }
                        }else {
                            throw new DataSdkException("范围 内容 {key:value} 使用有误 只允许一对键值");
                        }
                    }
                }
            }
        }

        //排序部分
        StringBuffer sortsql=new StringBuffer("");
        LinkedHashMap<String, Object> sort = query.getSort();
        if(sort.size()>0){
            sortsql.append(" ORDER BY ");
            int sortnum=0;
            for (Map.Entry<String, Object> entry : sort.entrySet()) {
                sortsql.append(sortnum==0?"":",");
                sortsql.append(entry.getKey()).append(" ").append(entry.getValue());
                sortnum++;
            }
        }

        StringBuffer sqlcountend = sqlcount.append(sqlcontent);
        System.out.println("查询总数sql:"+sqlcountend);

        //查具体数据
        StringBuffer sql=new StringBuffer("SELECT *  FROM ");
        //分页sql
        StringBuffer pagesql=new StringBuffer(" LIMIT ").append((current_page-1)*page_size).append(",").append(page_size);
        //最终执行sql
        StringBuffer sqlend = sql.append(sqlcontent).append(sortsql).append(pagesql);
        System.out.println("查询内容sql:"+sqlend);
        List<Map<String, Object>> list = queryForList(sqlend.toString(),objects);
        //查总数
        Integer total_count = queryCount(sqlcountend.toString(),objects);
        Integer total_page=(total_count/page_size)+1;
        if(current_page>total_page){
            throw new DataSdkException("Request currentPage > totalPages, you must check you request params about currentPage or pageSize");
        }

        Pagination pagination = new Pagination();
        pagination.setCurrent_page(current_page);//当前第几页
        pagination.setCount(total_count);//总条数
        pagination.setTotal_page(total_page);//总页数
        pagination.setPage_size(page_size);//每页有几条
        pagination.setData(list);

        return pagination;
    }

    /**
     * sql  不推荐
     * ***/
    @Deprecated
    public Pagination queryBySql(String sql) {
        return queryBySql(sql,1,10);
    }
    /*****
     * sql查询 不推荐
     * @param sql sql查询语句
     * @param page_size 当前页大小
     * @param current_page 当前页码
     * @return
     */
    @Deprecated
    public Pagination<Map> queryBySql(String sql,int current_page,int page_size){
       StringBuffer buffer=new StringBuffer(sql);

        System.out.println(sql);
        //不管什么sql 过来 查数量的时候最外面包一层
        Integer total_count = queryCountBySql("SELECT COUNT(1) FROM ("+sql.toString()+") A");
        List<Map<String, Object>> list = queryForListBySql(buffer.append(" LIMIT ").append((current_page-1)*page_size).append(",").append(page_size).toString());

        Integer total_page=(total_count/page_size)+1;

        Pagination pagination = new Pagination();
        pagination.setCurrent_page(current_page);
        pagination.setCount(list.size());
        pagination.setTotal_page(total_page);
        pagination.setPage_size(page_size);
        pagination.setData(list);
        return pagination;
    }
    /***
     * 插入数据
     * **/
    public String insert(String resource_name, Object data) {
        Gson gson = new Gson();
        Map<String,Object> query_data = gson.fromJson(gson.toJson(data), Map.class);
        ArrayList<Map<String,Object>> list = Lists.newArrayList();
        list.add(query_data);
        String ids="";
        try {
            ids = insertBatch(resource_name, list);
        } catch (SQLException e) {
            throw new DataSdkException(e.getMessage());
        }
        return ids.toString();
    }

    /***
     *  根据主键 更新数据
     * **/
    public String update(String resource_name, Object data) {
        Gson gson = new Gson();
        Map<String,Object> query_data = gson.fromJson(gson.toJson(data), Map.class);
        ArrayList<Map<String,Object>> list = Lists.newArrayList();
        list.add(query_data);
        String ids="";
        try {
            ids = updateBatch(resource_name, list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ids.toString();
    }

    /***
     * 删除数据 jdbc
     * **/
    public String delete(String resource_name, String... pkids) {
        String ids="";
        try {
            ids=deleteBatch(resource_name,pkids);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ids;
    }
}
